create  table jms_dm.dm_all_transfer_count_deatil_dt(
date_time date COMMENT '时间dt' ,
waybill_no varchar(100) COMMENT '单号' ,
taking_code      varchar(100) COMMENT '揽收网点code' ,
taking_name varchar(100) COMMENT '揽收网点name' ,
taking_network_type  varchar(100) COMMENT '揽收网点类型，网点类型:1总部,2代理区,3加盟商,4中心,5集散点,6网点' ,
taking_fran_code   varchar(100) COMMENT '揽收网点加盟商' ,
taking_fran_name    varchar(100) COMMENT '揽收网点加盟商code' ,
taking_agent_code   varchar(100) COMMENT '代理区编码' ,
taking_agent_name    varchar(100) COMMENT '代理区名称',
taking_manage_code   varchar(100) COMMENT '管理大区名称',
taking_manage_name    varchar(100) COMMENT '管理大区名称',
taking_provider_id     varchar(100) COMMENT '省份ID',
taking_provider_desc  varchar(100) COMMENT '省份',
taking_city_id         varchar(100) COMMENT '城市ID',
taking_city_desc    varchar(100) COMMENT '城市',
taking_area_id     varchar(100) COMMENT '区/县ID',
taking_area_desc    varchar(100) COMMENT '区/县',
taking_time       datetime COMMENT '揽收时间',
first_center_network_code  varchar(100) COMMENT '始发中心' ,
first_center_network_name varchar(100) COMMENT '始发中心name' ,
end_center_network_code  varchar(100) COMMENT '目的中心' ,
end_center_network_name varchar(100) COMMENT '目的中心name' ,
sign_network_code  varchar(100) COMMENT '签收网点' ,
sign_network_name   varchar(100) COMMENT '签收网点name' ,
sign_network_type   varchar(100) COMMENT '签收网点类型' ,
sign_fran_code   varchar(100) COMMENT '签收网点加盟商' ,
sign_fran_name    varchar(100) COMMENT '签收网点加盟商code' ,
sign_agent_code   varchar(100) COMMENT '代理区编码' ,
sign_agent_name    varchar(100) COMMENT '代理区名称',
sign_manage_code   varchar(100) COMMENT '管理大区名称',
sign_manage_name    varchar(100) COMMENT '管理大区名称',
sign_provider_id     varchar(100) COMMENT '省份ID',
sign_provider_desc  varchar(100) COMMENT '省份',
sign_city_id         varchar(100) COMMENT '城市ID',
sign_city_desc    varchar(100) COMMENT '城市',
sign_area_id     varchar(100) COMMENT '区/县ID',
sign_area_desc    varchar(100) COMMENT '区/县',
sign_time       datetime COMMENT '签收时间',
terminal_dispatch_code  varchar(100) COMMENT '原三段码' ,
terminal_network_code  varchar(100) COMMENT '原目的地' ,
terminal_network_name varchar(100) COMMENT '原目的地' ,
sj_route   varchar(500) COMMENT '实际路由' ,
sj_route_simple_name   varchar(500) COMMENT '实际路由简称' ,
sj_route_num bigint COMMENT '实际路由中转次数' ,
lianxu_name  varchar(500) COMMENT '连续出现的路由' ,
lianxu_name_num bigint COMMENT '连续出现的路由中转次数' ,
proble_type_subject_code  varchar(100) COMMENT '问题件类型' ,
proble_type_subject_name  varchar(100) COMMENT '问题件类型name' ,
sj_agent_in_count bigint COMMENT '实际路由代理区内中转次数' ,
sj_agent_out_count bigint COMMENT '实际路由代理区外中转次数' ,
gh_route varchar(500) COMMENT '规划路由' ,
gh_route_num bigint COMMENT '规划路由中转次数' ,
is_rational_flg bigint COMMENT '路由是否不合理，0合理，1不合理',
is_reback_flg bigint COMMENT   '是否退回件，0否，1是',
is_proble_flg bigint COMMENT   '是否问题件，0否，1是'
)
ENGINE = OLAP
DUPLICATE KEY( date_time,waybill_no,taking_code)
COMMENT "全程中转次数报表"
PARTITION BY RANGE(date_time)
(START ("2022-07-31") END ("2022-09-25") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`waybill_no`) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);

 ALTER TABLE jms_dm.dm_all_transfer_count_deatil_dt ADD COLUMN is_wrong_flg bigint  COMMENT '是否错发，0否，1是';

  alter table jms_dm.dm_all_transfer_count_deatil_dt add column (
  must_center_name varchar(500)  COMMENT "应交件中心",
  first_nodal_network_code varchar(500)  COMMENT "始发集散code",
  first_nodal_network_name varchar(500)  COMMENT "始发集散name",
  end_nodal_network_code varchar(500)  COMMENT "目的集散code",
  end_nodal_network_name varchar(500)  COMMENT "目的集散name",
  center_whole_route varchar(500)  COMMENT "中转路由"
  )  ;
